package com.demo.Test;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;

import java.io.File;
import java.sql.*;
import java.util.ArrayList;

public class Test7 {

    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        try{
            File file = new File("test.xls");
            Workbook wb = Workbook.getWorkbook(file);
            Sheet sheet = wb.getSheet(0);
            ArrayList<ArrayList> aa = new ArrayList<>();
            for(int i=1; i<sheet.getRows(); i++){
                ArrayList al = new ArrayList();
                for(int j=0; j<sheet.getColumns(); j++){
                    Cell cell = sheet.getCell(j, i);
                    al.add(cell.getContents());
                }
                aa.add(al);
            }
            System.out.println(aa);
            String url= "jdbc:mysql://106.13.7.203:3306/wg12?useCursorFetch=true&amp;autoReconnect=true&amp;useUnicode=true&amp;characterEncoding=UTF-8";
            String username = "root";
            String password = "Asd6250390@";
            Class.forName("com.mysql.jdbc.Driver");
            Connection conn = DriverManager.getConnection(url, username, password);
            System.out.println("连接成功");
            Statement st = conn.createStatement();
            ResultSet rs = st.executeQuery("select * from users");
            ArrayList al = new ArrayList();
            while (rs.next()){
                int id = rs.getInt("id");
                al.add(String.valueOf(id));
            }
            System.out.println(al);
            for(int i=0; i<aa.size(); i++) {
                if(al.contains(String.valueOf((aa.get(i)).get(0)))) {
                    System.out.println((aa.get(i)).get(0));
                    String sql = String.format("update users set age='%s' where id='%s'",
                            (aa.get(i)).get(3), (aa.get(i)).get(0));
                    PreparedStatement pst = conn.prepareStatement(sql);
                    pst.execute();
                }else{
                    String sql = String.format("insert into users(id, name, gender, age) values('%s', '%s', '%s', '%s')",
                            (aa.get(i)).get(0), (aa.get(i)).get(1), (aa.get(i)).get(2), (aa.get(i)).get(3));
                    PreparedStatement pst = conn.prepareStatement(sql);
                    pst.execute();
                }
            }
        }catch (Exception e){
            e.printStackTrace();
        }
    }
}
